OpenEdge Data Management:<br />DataServer for Microsoft SQL Server Block cursors

Block cursors are available as a performance enhancement. They allow blocks of records to be bound and fetched together from a single database request, thus reducing the network overhead and minimizing record copies for 4GL queries and finds, and minimizing record copies.

Considering block cursors and firehose cursors

Block cursors are the default behavior replacing look-ahead cursors for NO-LOCK queries that are processed on the server side. A NO-LOCK query is one that executes with the NO-LOCK lock condition attached to its 4GL statement.

Block cursors are the preferred choice to process NO-LOCK queries on the server side. However, server-side cursor processing is not the primary choice overall. Firehose cursors, providing optimum performance for NO-LOCK queries, surpasses the benefits of block cursors because they are client-based. For an in depth presentation of firehose cursors, see the "Firehose and Fast Forward-Only Cursors" section.

Additional details about block cursor behavior

DataServer connections that run at the read uncommitted isolation level will also execute queries that specify the SHARE-LOCK condition as NO-LOCK queries and will also utilize block cursors for their result sets. You can shut off block cursor behavior and revert back to look-ahead cursors for NO-LOCK queries by setting the -Dsrv PRGRS_BLOCK_CURS,0. If you leave block cursors on but wish to turn off the block and/or look-ahead cursor optimizations for a specific query, you can set the QUERY-TUNING option QUERY-TUNING(NO-LOOKAHEAD) on your 4GL statement.

While block cursors increase efficiency, they might also require more memory than look-ahead cursors. Block cursors will try to reuse memory from previous result sets whenever possible. You can adjust the memory available to block cursors according to your specifications. Adjust the cache size of an individual block with the same connection-level parameter you used to set the look-ahead cache size. The -Dsrv QT_CACHE_SIZE option allows you to specify at connect time how large the block cache should be for individual query results. When block cursors are active, the default QT_CACHE_SIZE is set to 10,000 bytes. When look-ahead cursors are active, this size defaults to 30,000 bytes.

You can override the connection-level QT_CACHE_SIZE at the statement level by setting the QUERY-TUNING option called CACHE-SIZE on the query itself. Determining the proper block size should be based on the maximum length of your returned records multiplied by the expected number of resultant rows and then compared to your available memory. Whenever an existing block is not reused, the block of memory allocated for the query will be adjusted downward to an exact multiple of the number of rows that can fit into the allocated area.

To prevent large applications from potentially overutilizing memory for block cursors, two other settings can be adjusted to limit the amount of memory available to block cursors. The first is the maximum block memory usage per table. If you have multiple queries open against the same table simultaneously, each query uses its own query block. The -Dsrv PRGRS_TABLE_BLOCKSIZE option puts an upper limit on the total memory available to query blocks of an individual table. This number should normally be set larger than the QT_CACHE_SIZE value. It can be set as high as two or three times the QT_CACHE_SIZE. If the maximum block memory available to the table will be exceeded by allocating space for the current NO-LOCK query in your 4GL, the query is instead executed with a look-ahead cursor. The default maximum block memory area per table is set at 65,000 bytes.

The second adjustment switch available is the -Dsrv PRGRS_MAX_BLOCKSIZE option. This value sets an upper limit to the overall memory provided to block cursors, irrespective of per table allocations. The default maximum is 1048576 bytes (or 1MB). Your accumulated total memory allocated for block cursors will not exceed this value. If allocating block cursor memory for a NO-LOCK query in your 4GL will cause this limit to be exceeded, the query would instead be executed with a look-ahead cursor. The PRGRS_MAX_BLOCKSIZE value should be set with respect to the amount of memory available on your machine. If you are running in client-server mode, the number should be set with respect to both the available memory on the server machine as well as the number of clients that will be connecting.

The following example uses the state table in the demo database, which has a maximum record size of 84 bytes:

FOR EACH state NO-LOCK QUERY-TUNING(CACHE-SIZE 850): DISPLAY state. END.

In this example, the QUERY-TUNING CACHE-SIZE value overrides the -Dsrv QT_CACHE_SIZE query block size default of 10,000 bytes. Ten records at 84 bytes per record totals 840 bytes in the query block. If a previously established block is located for that table that is greater than or equal to the requested size, it will be reused. However, if a new block is established instead, its memory allocation will be adjusted downward to 840 bytes in order to fit an exact multiple of rows in the block. Then, if the result set contains 30 records, the query block will get refilled three times before the end of the result set if read sequentially.

Block cursors on vs. off

Block cursors are the default over lookahead cursors when PRGRS_BLOCK_CURS is enabled and a server-side cursor is needed. The switch -Dsrv PRGRS_BLOCK_CURS,0 will turn off block cursors in SQL Server if you want to turn off the feature for this connection. You can always disable block cursors for a specific query by disabling lookahead with a query tuning option: QUERY-TUNING(NO-LOOKAHEAD). Block cursors is not a one size fits all feature. There are application environments where the default values for block allocation sizes can result in either under-utilization or over-utilization of memory. See the "Using the block cursor switches" section for information on tuning the amount of memory that block cursors utilizes.

For any given query, the use of a block cursor or a lookahead cursor is mutually exclusive. In certain circumstances, block cursors will downgrade to lookahead. These are the criteria:

When -Dsrv BINDING,0 is set. When binding is off, block cursors are disabled.

Predictable single record result sets — FOR FIRST/FOR LAST or dynamic find operations.

The PRGRS_TABLE_BLOCKSIZE has been exceeded and all existing block table space is in-use by open cursors.

The accumulation of memory allocated to all block cursors has reached PRGRS_MAX_BLOCKSIZE.

The QT_CACHE_SIZE query-tuning (CACHE-SIZE) value is not larger than 2 times the maximum row size. At least 2 rows need to fit in the result block to use a block cursor.

Note: The macro BLOCKBUF_AVAILABLE can be used to determine if the current query is or is not using a block cursor.

Block vs. lookahead cursors

Block cursors and lookahead cursors are both associated with NO-LOCK queries. These are queries where the lock status has been explicitly set to NO-LOCK or where the lock status has been explicitly set to SHARE-LOCK and the transaction isolation level is set to read-uncommitted. Block cursors and lookahead cursors are mutually exclusive.

Block cursors are more efficient than lookahead cursors for the following reasons:

The results of a query are bound directly to the area from which they are copied into the record buffer on the client. Lookahead cursors copy out of a common bind area into the lookahead cache and then are copied into client record buffer resulting in multiple copies of the data.

The fetch process used by block cursors fetches multiple rows at a time, reducing the number of driver calls and potentially reducing the total number of network round trips to the server.

Block cursor memory is preallocated prior to fetching a result set, whereas lookahead cursors post allocate memory as records are fetched from the result set.

Block cursors are directly linked to the result set binding feature whereas lookahead cursors have no particular dependency on whether binding or late-binding is active.